﻿CREATE  FUNCTION GetWaitStats

(

-- Add the parameters for the function here

 @ServerID int,
@TraceID int

)

RETURNS TABLE

AS

RETURN

(



select a.Name, a.wait_stat_s-b.wait_stat_s as delta , a.Time  from
(SELECT      w.Name,  wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s , t.Time
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID) and wd.PerfWaitstatsID   in (
Select b.PerfWaitstatsID from  (
select top 10 a.PerfWaitstatsID, c.wait_stat_s - a.wait_stat_s as deltatotal  from
(SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID)) as a

join

(
select PerfWaitstatsID , min(TimeID) as mintime, Max(timeID) as maxtime from(
SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s 
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID)) as wsdata Group by PerfWaitstatsID) as b

on a.PerfWaitstatsID = b.PerfWaitstatsID and (a.TimeID = b.mintime )

join
(SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID)) as c

on c.PerfWaitstatsID = b.PerfWaitstatsID and (c.TimeID = b.maxtime ) order by deltatotal desc
) as b) ) as a


join 

(SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID) and wd.PerfWaitstatsID   in (
Select b.PerfWaitstatsID from  (
select top 10 a.PerfWaitstatsID, c.wait_stat_s - a.wait_stat_s as deltatotal  from
(SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID)) as a

join

(
select PerfWaitstatsID , min(TimeID) as mintime, Max(timeID) as maxtime from(
SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s 
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID)) as wsdata Group by PerfWaitstatsID) as b

on a.PerfWaitstatsID = b.PerfWaitstatsID and (a.TimeID = b.mintime )

join
(SELECT        wd.PerfWaitstatsID, wd.TimeID, wd.wait_stat_s
FROM            Performance.PerfWaitstatsData AS wd INNER JOIN
                         Performance.PerfWaitstats AS w ON wd.PerfWaitstatsID = w.PerfWaitstatsID INNER JOIN
                         Performance.Time AS t ON wd.TimeID = t.TimeID INNER JOIN
                         Performance.Instance ON w.InstanceID = Performance.Instance.InstanceID INNER JOIN
                         Performance.PerfServer ON Performance.Instance.PerfServerID = Performance.PerfServer.PerfServerID
WHERE        (t.TraceID = @TraceID) AND (Performance.PerfServer.PerfServerID = @TraceID)) as c

on c.PerfWaitstatsID = b.PerfWaitstatsID and (c.TimeID = b.maxtime ) order by deltatotal desc
) as b) ) as b on a.TimeID = b.TimeID+@TraceID and a.PerfWaitstatsID = b.PerfWaitstatsID


)